USE master
go
IF DB_ID('AITSM') IS NOT NULL
DROP DATABASE AITSM
go
create database AITSM
go
use AITSM
go
CREATE TABLE [Admin](
	[UserName] [varchar](50) primary key,
	[Password] [varchar](50)
)

go
	CREATE TABLE Charges(
	[ChargesID] [int] IDENTITY(1,1) primary key,
	[To] [float] NOT NULL,
	[From] [float] NOT NULL,
	[Money] [int] NOT NULL
 )
go
CREATE TABLE Students(
	[StudentName] [varchar](50) NOT NULL,
	[RollNumber] [varchar](50) primary key,
	[DayOfBirth] [datetime] NOT NULL,
	[Address] [varchar](50) NOT NULL,
	[Phone] [int] NOT NULL,
	[Sex] [bit] NOT NULL,
	[Email] [varchar](50)
)
go

CREATE TABLE Fine(
	[FineID] [int] IDENTITY(1,1) primary key,
	[RollNumber] [varchar](50) REFERENCES Students(RollNumber),
	[POA] [float] NULL,
	[Money] [float] NULL,
	[Status] [bit] NULL
)
go
CREATE TABLE [TimeSlot](
	[HoursID] [char](10) primary key,
	[To] [int] NOT NULL,
	[From] [int] NOT NULL
 )
go
CREATE TABLE [Staff](
	[StaffName] [varchar](50) NOT NULL,
	[StaffUser] [varchar](50) primary key,
	[Password] [varchar](50) NOT NULL,
	[Sex] [bit] NOT NULL,
	[BirthOfDay] [datetime] NOT NULL,
	[Experience] [text] NOT NULL,
	[Status] bit default 0
)
go
CREATE TABLE [Courses](
	[CoursesName] [varchar](50) primary key,
	[Describe] [text] NOT NULL,
	[Fee] [int] NOT NULL,
	[Duration] [int] NOT NULL
)
go
CREATE TABLE [Subjects](
	[SubjectName] [varchar](50) NOT NULL,
	[HoursWeek] [int] NOT NULL,
	[TotalTime] [int] NOT NULL,
	[SubjectSymbol] [varchar](50) primary key
)
go
CREATE TABLE [Subject_Course](
	[SubjectSymbol] [varchar](50),
	[CourseName] [varchar](50),
	constraint pk_SubjectCount primary key (SubjectSymbol,CourseName),
	foreign key (CourseName) references Courses(CoursesName),
	foreign key (SubjectSymbol) references Subjects(SubjectSymbol),
)	
go
CREATE TABLE [Class](
	[ClassName] [varchar](50) primary key,
	[CourseName] [varchar](50) NOT NULL,
	[Semester] [int] NULL CONSTRAINT [DF_Class_Semester]  DEFAULT (1),
	[To] [datetime] NULL,
	[From] [datetime],
	foreign key (CourseName) references Courses(CoursesName)
)
go

CREATE TABLE [Student_class](
	[RollNumber] [varchar](50) ,
	[ClassName] [varchar](50),
	constraint pk_SubjectClass primary key (RollNumber,ClassName),
	foreign key (ClassName) references [Class](ClassName),
	foreign key (RollNumber) references Students(RollNumber)
)
go
CREATE TABLE [Week](
	[WeekID] [int] IDENTITY(1,1) primary key,
	[ClassName] [varchar](50) references [Class](ClassName),
	[Week] [int] NOT NULL,
	[To] [datetime] NULL,
	[From] [datetime]
	
)
go
CREATE TABLE [Schedule](
	[ScheduleID] [int] IDENTITY(1,1) primary key,
	[WeekID] [int] NOT NULL,
	[ClassName] [varchar](50) NOT NULL,
	[StaffUser] [varchar](50) NULL,
	[HoursID] [char](10) NOT NULL,
	[SubjectSymbol] [varchar](50) NULL,
	[Date] [datetime] NOT NULL,
	foreign key ([WeekID]) references [Week]([WeekID]),
	foreign key ([SubjectSymbol]) references [Subjects]([SubjectSymbol]),
	foreign key ([HoursID]) references TimeSlot([HoursID]),
	foreign key ([StaffUser]) references Staff([StaffUser]),
	foreign key ([ClassName]) references [Class]([ClassName])
)
go
CREATE TABLE [Attendance](
	[RollNumber] [varchar](50) NOT NULL,
	[ScheduleID] [int] NOT NULL,
	[Status] [bit] NOT NULL   DEFAULT (0),
	[Note] [varchar](50) NULL,
	[AttendanceStatus] [bit] default 0,
	constraint pk_Attendance primary key (RollNumber,ScheduleID),
	foreign key ([ScheduleID]) references [Schedule]([ScheduleID]),
	foreign key (RollNumber) references Students(RollNumber)
)

go